package data.admin;

import dao.SQLiteConnection;
import gui.admin.CostWin;
import javax.swing.*;
import java.sql.*;

public class Cost extends CostWin {

    Cost(){
        //查询按钮
        getSelect().addActionListener(e->{
            String rid=getT1().getText();
            String ISBN=getT2().getText();
            String bookname=getT3().getText();
            //生成sql语句
            if(!(rid.isEmpty()&&ISBN.isEmpty()&&bookname.isEmpty())){
                String sql1=
                        "SELECT Borrow.bid, Reader.rid, Record.ISBN, Book.bookname, Borrow.btime, Reader.rname " +
                        "from Borrow " +
                        "inner join Record on Record.bid=Borrow.bid " +
                        "inner join Reader on Reader.rid=Record.rid " +
                        "inner join Book on Book.ISBN=Record.ISBN " +
                        "where rtime is null";
                if(!rid.isEmpty()){
                    sql1=sql1+" AND rid="+rid;
                }
                if(!ISBN.isEmpty()){
                    sql1=sql1+" AND Borrow.ISBN='"+ISBN+"'";
                }
                if(!bookname.isEmpty()){
                    sql1=sql1+" AND bookname='"+bookname+"'";
                }
                //连接数据库
                SQLiteConnection connection=new SQLiteConnection();
                connection.Connect_SQLite();
                // 读取数据库中的数据
                try {
                    Connection conn = connection.getConnection();
                    Statement stmt = conn.createStatement();
                    ResultSet rs = stmt.executeQuery(sql1);
                    //清除原表格中的内容
                    getModel().setRowCount(0);
                    //添加新数据
                    ResultSetMetaData meta = rs.getMetaData();
                    int columnCount = meta.getColumnCount();
                    // 添加数据
                    while (rs.next()) {
                        Object[] rowData = new Object[columnCount];
                        for (int i = 1; i <= columnCount; i++) {
                            rowData[i - 1] = rs.getObject(i);
                        }
                        getModel().addRow(rowData);
                    }
                    getTable().setModel(getModel());    // 设置表格的数据和显示
                } catch (SQLException ex) {
                    throw new RuntimeException(ex);
                }
                connection.closeConnection();
            }
        });
        //刷新按钮
        getEmpty().addActionListener(e->{
            getT1().setText("");
            getT2().setText("");
            getT3().setText("");
            //清除表格内容
            getModel().setRowCount(0);
            //连接数据库
            SQLiteConnection connection = new SQLiteConnection();
            connection.Connect_SQLite();
            // 读取数据库中的数据
            try {
                Connection conn = connection.getConnection();
                Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery(
                        "SELECT Borrow.bid, Reader.rid, Record.ISBN, Book.bookname,Reader.rname,cost " +
                                "from Borrow " +
                                "inner join Record on Record.bid=Borrow.bid " +
                                "inner join Reader on Reader.rid=Record.rid " +
                                "inner join Book on Book.ISBN=Record.ISBN " +
                                "where rtime is not null and state is not NULL and cost is not null ");
                ResultSetMetaData meta = rs.getMetaData();
                int columnCount = meta.getColumnCount();
                // 添加数据
                while (rs.next()) {
                    Object[] rowData = new Object[columnCount];
                    for (int i = 1; i <= columnCount; i++) {
                        rowData[i - 1] = rs.getObject(i);
                    }
                    getModel().addRow(rowData);
                }
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
            connection.closeConnection();
        });
        //缴纳按钮
        getReturnbook().addActionListener(e->{
            int row =getTable().getSelectedRow();
            if(row < 0) {
                JOptionPane.showMessageDialog(this, "请选择记录!");
            }
            else {
                String bid = getTable().getValueAt(row, 0).toString();
                String rid = getTable().getValueAt(row, 1).toString();
                deleteCost(bid,rid);
            }
        });
    }

    // 缴纳过程
    private void deleteCost(String bid,String rid){
//        String rtime=getNowTime();
        SQLiteConnection connection=new SQLiteConnection();
        connection.Connect_SQLite();
        Connection conn=connection.getConnection();
        // cost设为null
        try {
            String sql="UPDATE Borrow SET cost = null WHERE bid='"+bid+"'";
            PreparedStatement stmt = conn.prepareStatement(sql);
            int rowsUpdated = stmt.executeUpdate();
            if(rowsUpdated>0){
                JOptionPane.showMessageDialog(this,"缴款成功！");
//                getEmpty().doClick();
            }
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
        // 读者解除限制
        try {
            String sql =
                    "UPDATE Reader " +
                    "SET state = NULL " +
                    "WHERE rid = '" + rid + "'" +
                    " AND NOT EXISTS (" +
                    "SELECT 1 " +
                    "FROM Borrow " +
                    "INNER JOIN Record ON Record.bid = Borrow.bid " +
                    "WHERE Record.rid = '"+rid+"' AND cost IS NOT NULL" +
                    ")";
            PreparedStatement stmt = conn.prepareStatement(sql);
            int rowsUpdated = stmt.executeUpdate();
            if(rowsUpdated>0){
                JOptionPane.showMessageDialog(this,"读者限制解除！");
//                getEmpty().doClick();
            }
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
        getEmpty().doClick();
    }

    public static void main(String[] args) {
        new Cost();
    }
}
